signon ;		
rsubmit;
libname prwora 	''; 
libname bankrup	'';
libname crime	'';
libname earn	'';
libname parearn '';
libname parssr '';
libname parmbr '';
libname sibssr '';
libname numi   '';

options compress=YES;

proc sort data=prwora.prwora_age18 out=crime.prwora_age18_crimekid ;
	by hun;
run;

data crime.prwora_age18_crimekid ;

	merge 
		crime.prwora_age18_crimekid
			(in=from_prworax
			keep=hun dob first_pay_dte n_diag1cat n_firstdiag toa n_male dibmdr dibdig race
					n_firstage pdscc age18_yes age18_final_con adultcdr_term rcd_est
					n_onssi_1990-n_onssi_2014 n_combpay_1990-n_combpay_2014)

		prwora.prwora_age18_collapse_suppmax
			(in=from_suppmaxx
			keep = hun n_pstatN02_1994-n_pstatN02_2014 n_pstatN22_1994-n_pstatN22_2014 n_pstatN25_1994-n_pstatN25_2014)

		prwora.prwora_age18_collapse_suppsum
			(in=from_suppsumx
			keep = hun combpmt_1995: combpmt_1996: combpmt_1997: combpmt_1998: combpmt_1999: combpmt_2000:
					   combpmt_2001: combpmt_2002: combpmt_2003: combpmt_2004: combpmt_2005:)

		bankrup.bankrup_ssrformerge 
			(in=from_namex
			rename=(ssn=hun lastn=fssr_lastn firstn=fssr_firstn middlen=fssr_middlen))
		;

		by hun;
		if from_prworax=1;
		from_prwora=from_prworax;
		from_suppmax=from_suppmaxx;
		from_suppsum=from_suppsumx;
		from_name=from_namex;
	run;

* See how many merge to name file ;
proc means data=crime.prwora_age18_crimekid ;
	var from_prwora from_suppmax from_suppsum from_name;
run;

* Merge in own earnings, parent earnings, parent SSI + DI, sibling SSI ;
proc sort data=numi.nss5 out=crime.numi_nodup nodupkey ;
	by ssn;
run;

proc sort data=numi.ndth out=crime.ndth_nodup nodupkey ;
	by ssn;
run;

data crime.prwora_age18_crimekid ;

	merge 
		crime.prwora_age18_crimekid
			(in=from_prworax)

		earn.prwora_age18_kidearns_followup 
			(in=from_earnx keep=hun 
			earn1990-earn2019)

		parearn.prwora_par_earn_hunmerge_wide 
			(in=from_parearnx  keep=hun 
			n_hhearns_1985-n_hhearns_2012
			n_hhearns1996_1985-n_hhearns1996_2012)

		parssr.prwora_par_ssr_hunmerge_wide 
			(in=from_parssrx keep=hun 
			n_hhcombpay_1985-n_hhcombpay_2012
			n_hhcombpay1996_1985-n_hhcombpay1996_2012)

		parmbr.prwora_par_mbr_hunmerge_wide 
			(in=from_parmbrx keep=hun 
			n_hhmbayr_1985-n_hhmbayr_2012
			n_hhmbayr1996_1985-n_hhmbayr1996_2012)

		sibssr.allssr_kids_wsibstats 
			(in=from_parmbrx keep=hun 
			n_sibpayall_1985-n_sibpayall_2012)

		crime.ndth_nodup
			(in=from_ndthx 
			keep = ssn dod_1 dod_2 dod_3
			rename = (ssn = hun dod_1 = numi_dod1 dod_2 = numi_dod2 dod_3 = numi_dod3))

		crime.numi_nodup
			(in=from_numix 
			keep = ssn dob race
			rename = (ssn = hun dob = numi_dob race = numi_race))
		;

		by hun ;
		if from_prworax=1 ;
		from_earn = from_earnx ;
		from_parearn = from_parearnx ;
		from_parssr = from_parssrx ;
		from_parmbr = from_parmbrx ;
		from_ndth = from_ndthx ;
		from_numi = from_numix ;
	run;

proc means data = crime.prwora_age18_crimekid ;
	var from_: ;
run ;

data crime.prwora_age18_crimekid ;
	set crime.prwora_age18_crimekid ;

	array hhmbr(1985:2012) n_hhmbayr_1985-n_hhmbayr_2012 ;
	array hhmbr1996(1985:2012) n_hhmbayr1996_1985-n_hhmbayr1996_2012 ;
	array hhssr(1985:2012) n_hhcombpay_1985-n_hhcombpay_2012 ;
	array hhssr1996(1985:2012) n_hhcombpay1996_1985-n_hhcombpay1996_2012 ;
	array sibssr(1985:2012) n_sibpayall_1985-n_sibpayall_2012 ;

	do i = 1985 to 2012 ;
		if hhmbr(i)		 = . then hhmbr(i) = 0 ;
		if hhmbr1996(i)  = . then hhmbr1996(i) = 0 ;
		if hhssr(i)  	 = . then hhssr(i) = 0 ;
		if hhssr1996(i)  = . then hhssr1996(i) = 0 ;
		if sibssr(i) 	 = . then sibssr(i) = 0 ;
	end ;
run ;

* CONVERT TO STATA;
%let DIR= ;
%macro stataexport(dsname);
proc export data=crime.&dsname
     file="&DIR\&dsname..dta"
     dbms=DTA REPLACE;
run;
%mend stataexport;

%stataexport(prwora_age18_crimekid);

endrsubmit;
signoff ;
